"""
数据源管理业务服务层
实现数据源的CRUD操作、连接测试、批量操作等业务逻辑
"""

import math
from datetime import datetime
from typing import Optional, List, Dict, Any
from sqlalchemy.ext.asyncio import AsyncSession
from sqlalchemy import select, and_, or_, func, desc
from sqlalchemy.orm import selectinload
from fastapi import HTTPException, status
import logging

from app.models.auth import User
from app.models.datasource import DataSource, DataSourceTestLog, DataSourceUsageStats, DatabaseType, TestStatus
from app.schemas.datasource import (
    DataSourceCreate, DataSourceUpdate, DataSourceResponse, DataSourceListResponse,
    ConnectionTestRequest, ConnectionTestResponse, TestLogResponse,
    BatchTestRequest, BatchTestResponse, DataSourceStats,
    DataSourceImportRequest, DataSourceImportResponse
)
from app.utils.encryption import encrypt_password, decrypt_password
from app.utils.database_connector import test_database_connection
from app.utils.kafka_client import KafkaClient
from app.utils.timezone_utils import get_shanghai_now
from app.core.logger import logger


class DataSourceService:
    """数据源管理服务类"""

    async def get_datasources(
        self,
        db: AsyncSession,
        page: int = 1,
        page_size: int = 10,
        search: Optional[str] = None,
        db_type: Optional[DatabaseType] = None,
        is_active: Optional[bool] = None,
        test_status: Optional[TestStatus] = None,
        current_user: User = None
    ) -> DataSourceListResponse:
        """获取数据源列表"""
        try:
            # 构建查询条件
            conditions = [DataSource.is_deleted == False]
            
            if search:
                search_condition = or_(
                    DataSource.name.ilike(f"%{search}%"),
                    DataSource.display_name.ilike(f"%{search}%"),
                    DataSource.host.ilike(f"%{search}%")
                )
                conditions.append(search_condition)
            
            if db_type:
                conditions.append(DataSource.type == db_type)
            
            if is_active is not None:
                conditions.append(DataSource.is_active == is_active)
            
            if test_status:
                conditions.append(DataSource.last_test_status == test_status)

            # 构建查询
            query = select(DataSource).where(and_(*conditions))
            
            # 计算总数
            count_query = select(func.count(DataSource.id)).where(and_(*conditions))
            count_result = await db.execute(count_query)
            total = count_result.scalar()

            # 分页和排序
            query = query.order_by(desc(DataSource.created_at))
            query = query.offset((page - 1) * page_size).limit(page_size)

            # 执行查询
            result = await db.execute(query)
            datasources = result.scalars().all()

            # 转换为响应模型
            datasource_responses = []
            for ds in datasources:
                datasource_responses.append(DataSourceResponse(
                    id=ds.id,
                    name=ds.name,
                    display_name=ds.display_name,
                    description=ds.description,
                    type=ds.type,
                    host=ds.host,
                    port=ds.port,
                    database_name=ds.database_name,
                    username=ds.username,
                    connection_params=ds.connection_params,
                    is_active=ds.is_active,
                    connection_timeout=ds.connection_timeout,
                    max_connections=ds.max_connections,
                    last_test_at=ds.last_test_at,
                    last_test_status=ds.last_test_status,
                    last_test_message=ds.last_test_message,
                    connection_string_masked=ds.connection_string_masked,
                    is_healthy=ds.is_healthy,
                    created_by=ds.created_by,
                    updated_by=ds.updated_by,
                    created_at=ds.created_at,
                    updated_at=ds.updated_at
                ))

            total_pages = math.ceil(total / page_size)

            return DataSourceListResponse(
                datasources=datasource_responses,
                total=total,
                page=page,
                page_size=page_size,
                total_pages=total_pages
            )

        except Exception as e:
            logger.error(f"获取数据源列表失败: {str(e)}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="获取数据源列表失败"
            )

    async def get_datasource_by_id(
        self,
        db: AsyncSession,
        datasource_id: int,
        current_user: User
    ) -> DataSourceResponse:
        """根据ID获取数据源详情"""
        try:
            stmt = select(DataSource).where(
                and_(
                    DataSource.id == datasource_id,
                    DataSource.is_deleted == False
                )
            )
            result = await db.execute(stmt)
            datasource = result.scalar_one_or_none()

            if not datasource:
                raise HTTPException(
                    status_code=status.HTTP_404_NOT_FOUND,
                    detail="数据源不存在"
                )

            return DataSourceResponse(
                id=datasource.id,
                name=datasource.name,
                display_name=datasource.display_name,
                description=datasource.description,
                type=datasource.type,
                host=datasource.host,
                port=datasource.port,
                database_name=datasource.database_name,
                username=datasource.username,
                connection_params=datasource.connection_params,
                is_active=datasource.is_active,
                connection_timeout=datasource.connection_timeout,
                max_connections=datasource.max_connections,
                last_test_at=datasource.last_test_at,
                last_test_status=datasource.last_test_status,
                last_test_message=datasource.last_test_message,
                connection_string_masked=datasource.connection_string_masked,
                is_healthy=datasource.is_healthy,
                created_by=datasource.created_by,
                updated_by=datasource.updated_by,
                created_at=datasource.created_at,
                updated_at=datasource.updated_at
            )

        except HTTPException:
            raise
        except Exception as e:
            logger.error(f"获取数据源详情失败: {str(e)}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="获取数据源详情失败"
            )

    async def create_datasource(
        self,
        db: AsyncSession,
        datasource_data: DataSourceCreate,
        current_user: User
    ) -> DataSourceResponse:
        """创建数据源"""
        try:
            # 检查名称是否已存在
            stmt = select(DataSource).where(
                and_(
                    DataSource.name == datasource_data.name,
                    DataSource.is_deleted == False
                )
            )
            result = await db.execute(stmt)
            if result.scalar_one_or_none():
                raise HTTPException(
                    status_code=status.HTTP_400_BAD_REQUEST,
                    detail="数据源名称已存在"
                )

            # 加密密码
            encrypted_password = encrypt_password(datasource_data.password)

            # 创建数据源
            datasource = DataSource(
                name=datasource_data.name,
                display_name=datasource_data.display_name,
                description=datasource_data.description,
                type=datasource_data.type,
                host=datasource_data.host,
                port=datasource_data.port,
                database_name=datasource_data.database_name,
                username=datasource_data.username,
                password_encrypted=encrypted_password,
                connection_params=datasource_data.connection_params,
                is_active=datasource_data.is_active,
                connection_timeout=datasource_data.connection_timeout,
                max_connections=datasource_data.max_connections,
                created_by=current_user.id,
                updated_by=current_user.id
            )

            db.add(datasource)
            await db.commit()
            await db.refresh(datasource)

            logger.info(f"用户 {current_user.username} 创建了数据源: {datasource.name}")

            return DataSourceResponse(
                id=datasource.id,
                name=datasource.name,
                display_name=datasource.display_name,
                description=datasource.description,
                type=datasource.type,
                host=datasource.host,
                port=datasource.port,
                database_name=datasource.database_name,
                username=datasource.username,
                connection_params=datasource.connection_params,
                is_active=datasource.is_active,
                connection_timeout=datasource.connection_timeout,
                max_connections=datasource.max_connections,
                last_test_at=datasource.last_test_at,
                last_test_status=datasource.last_test_status,
                last_test_message=datasource.last_test_message,
                connection_string_masked=datasource.connection_string_masked,
                is_healthy=datasource.is_healthy,
                created_by=datasource.created_by,
                updated_by=datasource.updated_by,
                created_at=datasource.created_at,
                updated_at=datasource.updated_at
            )

        except HTTPException:
            raise
        except Exception as e:
            logger.error(f"创建数据源失败: {str(e)}")
            await db.rollback()
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="创建数据源失败"
            )

    async def update_datasource(
        self,
        db: AsyncSession,
        datasource_id: int,
        datasource_data: DataSourceUpdate,
        current_user: User
    ) -> DataSourceResponse:
        """更新数据源"""
        try:
            # 获取数据源
            stmt = select(DataSource).where(
                and_(
                    DataSource.id == datasource_id,
                    DataSource.is_deleted == False
                )
            )
            result = await db.execute(stmt)
            datasource = result.scalar_one_or_none()

            if not datasource:
                raise HTTPException(
                    status_code=status.HTTP_404_NOT_FOUND,
                    detail="数据源不存在"
                )

            # 更新字段
            update_data = datasource_data.dict(exclude_unset=True)
            
            # 处理密码加密
            if 'password' in update_data:
                update_data['password_encrypted'] = encrypt_password(update_data.pop('password'))

            # 更新数据源
            for field, value in update_data.items():
                setattr(datasource, field, value)
            
            datasource.updated_by = current_user.id
            datasource.updated_at = get_shanghai_now()

            await db.commit()
            await db.refresh(datasource)

            logger.info(f"用户 {current_user.username} 更新了数据源: {datasource.name}")

            return DataSourceResponse(
                id=datasource.id,
                name=datasource.name,
                display_name=datasource.display_name,
                description=datasource.description,
                type=datasource.type,
                host=datasource.host,
                port=datasource.port,
                database_name=datasource.database_name,
                username=datasource.username,
                connection_params=datasource.connection_params,
                is_active=datasource.is_active,
                connection_timeout=datasource.connection_timeout,
                max_connections=datasource.max_connections,
                last_test_at=datasource.last_test_at,
                last_test_status=datasource.last_test_status,
                last_test_message=datasource.last_test_message,
                connection_string_masked=datasource.connection_string_masked,
                is_healthy=datasource.is_healthy,
                created_by=datasource.created_by,
                updated_by=datasource.updated_by,
                created_at=datasource.created_at,
                updated_at=datasource.updated_at
            )

        except HTTPException:
            raise
        except Exception as e:
            logger.error(f"更新数据源失败: {str(e)}")
            await db.rollback()
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="更新数据源失败"
            )

    async def delete_datasource(
        self,
        db: AsyncSession,
        datasource_id: int,
        current_user: User
    ) -> Dict[str, Any]:
        """删除数据源（软删除）"""
        try:
            # 获取数据源
            stmt = select(DataSource).where(
                and_(
                    DataSource.id == datasource_id,
                    DataSource.is_deleted == False
                )
            )
            result = await db.execute(stmt)
            datasource = result.scalar_one_or_none()

            if not datasource:
                raise HTTPException(
                    status_code=status.HTTP_404_NOT_FOUND,
                    detail="数据源不存在"
                )

            # 软删除
            datasource.soft_delete(current_user.id)

            await db.commit()

            logger.info(f"用户 {current_user.username} 删除了数据源: {datasource.name}")

            return {
                "id": datasource_id,
                "message": "数据源删除成功"
            }

        except HTTPException:
            raise
        except Exception as e:
            logger.error(f"删除数据源失败: {str(e)}")
            await db.rollback()
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="删除数据源失败"
            )

    async def test_datasource_connection(
        self,
        db: AsyncSession,
        datasource_id: int,
        current_user: User
    ) -> ConnectionTestResponse:
        """测试数据源连接"""
        try:
            # 获取数据源
            stmt = select(DataSource).where(
                and_(
                    DataSource.id == datasource_id,
                    DataSource.is_deleted == False
                )
            )
            result = await db.execute(stmt)
            datasource = result.scalar_one_or_none()

            if not datasource:
                raise HTTPException(
                    status_code=status.HTTP_404_NOT_FOUND,
                    detail="数据源不存在"
                )

            # 解密密码
            decrypted_password = decrypt_password(datasource.password_encrypted)

            # 构建测试请求
            test_request = ConnectionTestRequest(
                type=datasource.type,
                host=datasource.host,
                port=datasource.port,
                database_name=datasource.database_name,
                username=datasource.username,
                password=decrypted_password,
                connection_params=datasource.connection_params,
                connection_timeout=datasource.connection_timeout
            )

            # 执行连接测试
            test_result = await test_database_connection(test_request)

            # 更新数据源测试结果
            test_status = TestStatus.SUCCESS if test_result.success else TestStatus.FAILED
            datasource.update_test_result(test_status, test_result.message)

            # 记录测试日志
            test_log = DataSourceTestLog(
                datasource_id=datasource.id,
                test_status=test_status,
                test_message=test_result.message,
                response_time=test_result.response_time,
                error_details=test_result.error_details,
                test_params={
                    "host": datasource.host,
                    "port": datasource.port,
                    "database": datasource.database_name,
                    "username": datasource.username
                },
                tested_by=current_user.id
            )

            db.add(test_log)
            await db.commit()

            logger.info(f"用户 {current_user.username} 测试了数据源连接: {datasource.name}, 结果: {test_status}")

            return test_result

        except HTTPException:
            raise
        except Exception as e:
            logger.error(f"测试数据源连接失败: {str(e)}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="测试数据源连接失败"
            )

    async def test_connection_params(
        self,
        db: AsyncSession,
        test_request: ConnectionTestRequest,
        current_user: User
    ) -> ConnectionTestResponse:
        """测试连接参数（不保存到数据库）"""
        try:
            # 执行连接测试
            test_result = await test_database_connection(test_request)

            logger.info(f"用户 {current_user.username} 测试了连接参数: {test_request.host}:{test_request.port}")

            return test_result

        except Exception as e:
            logger.error(f"测试连接参数失败: {str(e)}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="测试连接参数失败"
            )

    async def batch_test_datasources(
        self,
        db: AsyncSession,
        request: BatchTestRequest,
        current_user: User
    ) -> BatchTestResponse:
        """批量测试数据源连接"""
        try:
            results = []
            success_count = 0
            failed_count = 0

            # 获取数据源列表
            stmt = select(DataSource).where(
                and_(
                    DataSource.id.in_(request.datasource_ids),
                    DataSource.is_deleted == False
                )
            )
            result = await db.execute(stmt)
            datasources = result.scalars().all()

            for datasource in datasources:
                try:
                    # 解密密码
                    decrypted_password = decrypt_password(datasource.password_encrypted)

                    # 构建测试请求
                    test_request = ConnectionTestRequest(
                        type=datasource.type,
                        host=datasource.host,
                        port=datasource.port,
                        database_name=datasource.database_name,
                        username=datasource.username,
                        password=decrypted_password,
                        connection_params=datasource.connection_params,
                        connection_timeout=datasource.connection_timeout
                    )

                    # 执行连接测试
                    test_result = await test_database_connection(test_request)

                    # 更新数据源测试结果
                    test_status = TestStatus.SUCCESS if test_result.success else TestStatus.FAILED
                    datasource.update_test_result(test_status, test_result.message)

                    # 记录测试日志
                    test_log = DataSourceTestLog(
                        datasource_id=datasource.id,
                        test_status=test_status,
                        test_message=test_result.message,
                        response_time=test_result.response_time,
                        error_details=test_result.error_details,
                        tested_by=current_user.id
                    )
                    db.add(test_log)

                    results.append({
                        "datasource_id": datasource.id,
                        "datasource_name": datasource.name,
                        "success": test_result.success,
                        "message": test_result.message,
                        "response_time": test_result.response_time
                    })

                    if test_result.success:
                        success_count += 1
                    else:
                        failed_count += 1

                except Exception as e:
                    failed_count += 1
                    results.append({
                        "datasource_id": datasource.id,
                        "datasource_name": datasource.name,
                        "success": False,
                        "message": f"测试失败: {str(e)}",
                        "response_time": None
                    })

            await db.commit()

            logger.info(f"用户 {current_user.username} 批量测试了 {len(datasources)} 个数据源")

            return BatchTestResponse(
                results=results,
                success_count=success_count,
                failed_count=failed_count,
                total_count=len(datasources)
            )

        except Exception as e:
            logger.error(f"批量测试数据源失败: {str(e)}")
            await db.rollback()
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="批量测试数据源失败"
            )

    async def batch_delete_datasources(
        self,
        db: AsyncSession,
        datasource_ids: List[int],
        current_user: User
    ) -> Dict[str, Any]:
        """批量删除数据源"""
        try:
            # 获取数据源列表
            stmt = select(DataSource).where(
                and_(
                    DataSource.id.in_(datasource_ids),
                    DataSource.is_deleted == False
                )
            )
            result = await db.execute(stmt)
            datasources = result.scalars().all()

            deleted_count = 0
            for datasource in datasources:
                datasource.soft_delete(current_user.id)
                deleted_count += 1

            await db.commit()

            logger.info(f"用户 {current_user.username} 批量删除了 {deleted_count} 个数据源")

            return {
                "deleted_count": deleted_count,
                "total_requested": len(datasource_ids),
                "message": f"成功删除 {deleted_count} 个数据源"
            }

        except Exception as e:
            logger.error(f"批量删除数据源失败: {str(e)}")
            await db.rollback()
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="批量删除数据源失败"
            )

    async def batch_toggle_datasources(
        self,
        db: AsyncSession,
        datasource_ids: List[int],
        is_active: bool,
        current_user: User
    ) -> Dict[str, Any]:
        """批量启用/禁用数据源"""
        try:
            # 获取数据源列表
            stmt = select(DataSource).where(
                and_(
                    DataSource.id.in_(datasource_ids),
                    DataSource.is_deleted == False
                )
            )
            result = await db.execute(stmt)
            datasources = result.scalars().all()

            updated_count = 0
            for datasource in datasources:
                datasource.is_active = is_active
                datasource.updated_by = current_user.id
                datasource.updated_at = get_shanghai_now()
                updated_count += 1

            await db.commit()

            action = "启用" if is_active else "禁用"
            logger.info(f"用户 {current_user.username} 批量{action}了 {updated_count} 个数据源")

            return {
                "updated_count": updated_count,
                "total_requested": len(datasource_ids),
                "action": action,
                "message": f"成功{action} {updated_count} 个数据源"
            }

        except Exception as e:
            logger.error(f"批量操作数据源失败: {str(e)}")
            await db.rollback()
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="批量操作数据源失败"
            )

    async def get_datasource_stats(
        self,
        db: AsyncSession,
        current_user: User
    ) -> DataSourceStats:
        """获取数据源统计信息"""
        try:
            # 总数统计
            total_stmt = select(func.count(DataSource.id)).where(DataSource.is_deleted == False)
            total_result = await db.execute(total_stmt)
            total_count = total_result.scalar()

            # 活跃数统计
            active_stmt = select(func.count(DataSource.id)).where(
                and_(DataSource.is_deleted == False, DataSource.is_active == True)
            )
            active_result = await db.execute(active_stmt)
            active_count = active_result.scalar()

            inactive_count = total_count - active_count

            # 健康状态统计
            healthy_stmt = select(func.count(DataSource.id)).where(
                and_(
                    DataSource.is_deleted == False,
                    DataSource.is_active == True,
                    DataSource.last_test_status == TestStatus.SUCCESS
                )
            )
            healthy_result = await db.execute(healthy_stmt)
            healthy_count = healthy_result.scalar()

            unhealthy_count = total_count - healthy_count

            # 数据库类型分布
            type_stmt = select(DataSource.type, func.count(DataSource.id)).where(
                DataSource.is_deleted == False
            ).group_by(DataSource.type)
            type_result = await db.execute(type_stmt)
            type_distribution = {row[0]: row[1] for row in type_result.fetchall()}

            # 最近测试结果
            recent_tests_stmt = select(DataSourceTestLog).options(
                selectinload(DataSourceTestLog.datasource)
            ).order_by(desc(DataSourceTestLog.tested_at)).limit(10)
            recent_tests_result = await db.execute(recent_tests_stmt)
            recent_tests = recent_tests_result.scalars().all()

            recent_test_results = []
            for test in recent_tests:
                if test.datasource and not test.datasource.is_deleted:
                    recent_test_results.append({
                        "datasource_name": test.datasource.name,
                        "test_status": test.test_status,
                        "test_message": test.test_message,
                        "response_time": test.response_time,
                        "tested_at": test.tested_at
                    })

            return DataSourceStats(
                total_count=total_count,
                active_count=active_count,
                inactive_count=inactive_count,
                healthy_count=healthy_count,
                unhealthy_count=unhealthy_count,
                type_distribution=type_distribution,
                recent_test_results=recent_test_results
            )

        except Exception as e:
            logger.error(f"获取数据源统计失败: {str(e)}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="获取数据源统计失败"
            )

    async def get_datasource_test_logs(
        self,
        db: AsyncSession,
        datasource_id: int,
        page: int = 1,
        page_size: int = 10,
        current_user: User = None
    ) -> Dict[str, Any]:
        """获取数据源测试日志"""
        try:
            # 验证数据源存在
            ds_stmt = select(DataSource).where(
                and_(DataSource.id == datasource_id, DataSource.is_deleted == False)
            )
            ds_result = await db.execute(ds_stmt)
            if not ds_result.scalar_one_or_none():
                raise HTTPException(
                    status_code=status.HTTP_404_NOT_FOUND,
                    detail="数据源不存在"
                )

            # 查询测试日志
            conditions = [DataSourceTestLog.datasource_id == datasource_id]

            # 计算总数
            count_stmt = select(func.count(DataSourceTestLog.id)).where(and_(*conditions))
            count_result = await db.execute(count_stmt)
            total = count_result.scalar()

            # 分页查询
            logs_stmt = select(DataSourceTestLog).where(and_(*conditions)).order_by(
                desc(DataSourceTestLog.tested_at)
            ).offset((page - 1) * page_size).limit(page_size)

            logs_result = await db.execute(logs_stmt)
            logs = logs_result.scalars().all()

            # 转换为响应格式
            log_responses = []
            for log in logs:
                log_responses.append(TestLogResponse(
                    id=log.id,
                    test_status=log.test_status,
                    test_message=log.test_message,
                    response_time=log.response_time,
                    error_details=log.error_details,
                    tested_by=log.tested_by,
                    tested_at=log.tested_at
                ))

            total_pages = math.ceil(total / page_size)

            return {
                "logs": log_responses,
                "total": total,
                "page": page,
                "page_size": page_size,
                "total_pages": total_pages
            }

        except HTTPException:
            raise
        except Exception as e:
            logger.error(f"获取测试日志失败: {str(e)}")
            raise HTTPException(
                status_code=status.HTTP_500_INTERNAL_SERVER_ERROR,
                detail="获取测试日志失败"
            )


# 创建全局服务实例
datasource_service = DataSourceService()
